﻿
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_CreateAsignUserMenu')
	BEGIN
		DROP  Procedure  [esf_sso].P_CreateAsignUserMenu
	END

GO

CREATE Procedure [esf_sso].P_CreateAsignUserMenu
	@pID					INT OUT
	
	,@pApplicationCode		VARCHAR (60)
	,@pUserGroupCode		VARCHAR (60) = NULL
	,@pParentCode			VARCHAR (60) = NULL
	,@pCode					VARCHAR (60)
	,@pName					VARCHAR (60)
	,@pDescription			VARCHAR (600) = NULL
	,@pExtra_I				VARCHAR (500) = NULL
	,@pURL					VARCHAR (1000) = NULL
	,@pIconURL				VARCHAR (4000) = NULL
	,@pHeaderURL			VARCHAR (4000) = NULL
	,@pUserCreationAlias	VARCHAR (20) = 'root'
	,@pCreationDateTime		DATETIME = NULL
	,@pIsEnabled			BIT = 1
AS

	DECLARE @ParentID		INT
	DECLARE @AdminGroupID	INT
	DECLARE @ApplicationID	INT
	DECLARE @UserGroupID	INT


	SET @pID = NULL
	SET @ParentID = NULL

	IF @pCreationDateTime IS NULL
		SET @pCreationDateTime = GETDATE ()


	print ' - --------------------------- -'
	print ' - Buscando menu por codigo ' + @pCode
	SELECT @pID = ID FROM [esf_sso].[BasicReference] WHERE BasicReferenceTypeID = 7 AND Code = @pCode
	print ' - Identificador del menu ' + ISNULL (CONVERT (VARCHAR (20), @pID), ' [ No existe ]')
	
	SELECT @ApplicationID = ID FROM esf_sso.VApplication WHERE Code = @pApplicationCode
	
	SELECT @AdminGroupID = ID FROM [esf_sso].VUserGroup WHERE Code = 'Administrators'
	
	
	IF @pParentCode IS NOT NULL
	BEGIN
		SELECT @ParentID = ID FROM [esf_sso].[BasicReference] WHERE BasicReferenceTypeID = 7 AND Code = @pParentCode
		print ' - Identificador del padre: ' + CONVERT (VARCHAR (20), @ParentID)
	END
	
	
	IF @pID IS NULL
	BEGIN
		print ' - Menu por codigo' + @pCode + ' no existe se procede a insertarlo...'
	
		INSERT INTO [esf_sso].[BasicReference] ([BasicReferenceTypeID],[Code],[Name],[Description],[Extra_I],[IsEnabled],[CreationDateTime],UserCreationAlias, Extra_II)
		SELECT 7, @pCode, @pName, @pDescription, @pExtra_I, @pIsEnabled, @pCreationDateTime,@pUserCreationAlias, @pURL

		SET @pID = @@IDENTITY
	END
	

	IF @ParentID IS NULL
	BEGIN
		
		IF NOT EXISTS (SELECT TOP 1 1 FROM [esf_sso].[TreeBasicReference] 
			WHERE [ParentBasicReferenceID] = @pID AND BasicReferenceID IS NULL)
		BEGIN
			print ' - Creando relacion padre unico para ' + CONVERT (VARCHAR (20), @pID)
		
			INSERT INTO [esf_sso].[TreeBasicReference] ([BasicReferenceID],[ParentBasicReferenceID])
				SELECT NULL, @pID
		END

	END
	ELSE
	BEGIN

		IF NOT EXISTS (SELECT TOP 1 1 FROM [esf_sso].[TreeBasicReference] 
			WHERE [BasicReferenceID] = @pID AND [ParentBasicReferenceID] = @ParentID)
		BEGIN
		
			print ' - Creando relacion padre ' + CONVERT (VARCHAR (20), @ParentID) + ' -> ' + CONVERT (VARCHAR (20), @pID)
		
			INSERT INTO [esf_sso].[TreeBasicReference] ([BasicReferenceID],[ParentBasicReferenceID])
				SELECT @pID, @ParentID
		END

	END


	IF NOT EXISTS (SELECT TOP 1 1 FROM [esf_sso].[TreeBasicReference] 
		WHERE [BasicReferenceID] = @pID AND [ParentBasicReferenceID] = @ApplicationID)
	BEGIN
		print ' - Creando parentesco hijo con app'
	
		INSERT INTO [esf_sso].[TreeBasicReference] ([BasicReferenceID],[ParentBasicReferenceID])
		VALUES (@pID, @ApplicationID);
	END

	
	IF @pUserGroupCode IS NOT NULL
	BEGIN
	
		SELECT @UserGroupID = ID FROM esf_sso.VUserGroup WHERE Code = @pUserGroupCode
	
		IF NOT EXISTS (SELECT TOP 1 1 FROM [esf_sso].[TreeBasicReference] 
			WHERE [BasicReferenceID] = @pID AND [ParentBasicReferenceID] = @UserGroupID)
		BEGIN	
			print ' - Creando parentesco hijo con grupo de usuario'
		
			INSERT INTO [esf_sso].[TreeBasicReference] ([BasicReferenceID],[ParentBasicReferenceID])
			SELECT @pID, @UserGroupID
		END
	
	END
	
	IF NOT EXISTS (SELECT TOP 1 1 FROM esf_sso.[TreeBasicReference]
		WHERE [BasicReferenceID] = @pID AND [ParentBasicReferenceID] = @AdminGroupID)
	BEGIN
		print ' - Creando parentesco hijo con grupo administrativo'	
	
		INSERT INTO esf_sso.[TreeBasicReference] ([ParentBasicReferenceID], [BasicReferenceID])
		VALUES (@AdminGroupID, @pID)
	END


	--DECLARE @ImageID INT


	--IF @pIconURL IS NOT NULL
	--BEGIN
	--	SELECT @ImageID = ID FROM esf_files.[File] WHERE URL = @pIconURL

	--	IF @ImageID IS NULL
	--	BEGIN
	--		INSERT INTO esf_files.[File] (FileTypeID, URL)
	--		SELECT
	--			(SELECT ID FROM esf_sso.VFileCategory WHERE Code = 'Icon_16x16')
	--			,@pIconURL		
			
	--		SET @ImageID = @@IDENTITY
	--	END

	--	INSERT INTO esf_sso.BasicReference_File (BasicReferenceID, FileID)
	--	VALUES (@pID, @ImageID)
	--END
	
	
	--IF @pHeaderURL IS NOT NULL 
	--BEGIN
	--	SELECT @ImageID = ID FROM esf_files.[File] WHERE URL = @pHeaderURL

	--	IF @ImageID IS NULL
	--	BEGIN
	--		INSERT INTO esf_files.[File] (FileTypeID, URL)
	--		SELECT
	--			(SELECT ID FROM esf_sso.VFileCategory WHERE Code = 'Header_400x50')
	--			,@pHeaderURL

	--		SET @ImageID = @@IDENTITY
	--	END

	--	INSERT INTO esf_sso.BasicReference_File (BasicReferenceID, FileID)
	--	VALUES (@pID, @ImageID)
	--END
	
GO
